﻿using System;

using System.Linq;
using System.Web;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
/// Summary description for EmployeeDAL
/// </summary>

    public class CardPriceDAL:ICardPriceDAL
    {
        public CardPriceDAL()
        {
            //
            // TODO: Add constructor logic here
            //
        }


        #region CardPriceDAL Members

        /// <summary>
        /// GetAll
        /// </summary>
        /// <returns>List<cardPrice></returns>
        public  List<CardPrice> GetAll()
        {
            string strSQL = @"SELECT
	                            P.ID
	                            ,P.Price
	                            ,T.Name AS CardType
	                            ,D.Name AS DayNight
                                ,E.FirstName
                                ,E.LastName                           
                            FROM
                                CardPrice P
                            LEFT JOIN   
                                Employee E  ON E.EmployeeID=P.LastUser
                            LEFT JOIN
	                            DayNight D ON D.DayNightID=P.DayNightID
                            LEFT JOIN
	                            CardType T ON T.CardTypeID=P.CardTypeID	
                            ORDER BY CardType ASC,DayNight DESC";
            SqlDataReader reader;
            List<CardPrice> list = null;
            CardPrice cardPrice = null;
            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
                if (reader.HasRows)
                {
                    list = new List<CardPrice>();
                    while (reader.Read())
                    {
                        cardPrice = new CardPrice();
                        cardPrice.Id = (Guid)reader["ID"];
                        cardPrice.Price = long.Parse(reader["Price"].ToString());
                        CardType type = new CardType();
                        type.Name = reader["CardType"].ToString();
                        cardPrice.CardType = type;

                        DayNight dayNight = new DayNight();
                        dayNight.Name = reader["DayNight"].ToString();
                        cardPrice.DayNight = dayNight;

                        Employee emp = new Employee();
                        emp.FirstName = reader["FirstName"].ToString();
                        emp.LastName = reader["LastName"].ToString();
                        cardPrice.Employee = emp;
                        list.Add(cardPrice);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool InsertOrUpdate(CardPrice cardPrice)
        {

            #region Sql sentence
            string strSQLInsert = @"INSERT INTO [CardPrice] (
	                                    [ID],
	                                    [DayNightID],
	                                    [CardTypeID],
	                                    [Price],
	                                    [LastUser],
	                                    [LastUpdate]
                                    ) VALUES (
	                                    NEWID(),
	                                    @DayNightID,
	                                    @CardTypeID,
	                                    @Price,
	                                    @LastUser,
	                                    GETDATE()
                                    )";

            string strSQLUpdate = @"UPDATE [CardPrice] SET
	                                    [DayNightID] = @DayNightID,
	                                    [CardTypeID] = @CardTypeID,
	                                    [Price] = @Price,
	                                    [LastUser] = @LastUser,
	                                    [LastUpdate] = GETDATE()
                                    WHERE
	                                    [ID] = @ID";
            #endregion

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@DayNightID",cardPrice.DayNight.Id),
                        new SqlParameter("@CardTypeID",cardPrice.CardType.Id),
                        new SqlParameter("@Price",cardPrice.Price),
                        new SqlParameter("@LastUser",cardPrice.LastUser),
                        new SqlParameter("@ID",cardPrice.Id)
                    };

            string strSQL;
            //Insert case
            if (cardPrice.Id == Guid.Empty)
            {
                strSQL = strSQLInsert;
            }
            //delete case
            else
            {
                strSQL = strSQLUpdate;
            }

            try
            {
                return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parms) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public CardPrice GetById(Guid id)
        {
            string strSQL = @"SELECT 
	                            P.ID
	                            ,P.CardTypeID
	                            ,P.DayNightID
	                            ,P.Price
                            FROM
	                            CardPrice P
                            INNER JOIN 	CardType T ON T.CardTypeID=P.CardTypeID
                            INNER JOIN 	DayNight D ON D.DayNightID=P.DayNightID
                            LEFT JOIN 	Employee E ON E.EmployeeID=P.LastUser
                            WHERE ID=@ID";
            SqlDataReader reader;
            CardPrice cardPrice = null;

            //parms
            SqlParameter parm = new SqlParameter("@ID", id);

            try
            {
                reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL, parm);

                if (reader.HasRows)
                {
                    reader.Read();
                    cardPrice = new CardPrice();
                    cardPrice.Id = (Guid)reader["ID"];
                    cardPrice.Price = long.Parse(reader["Price"].ToString());
                    cardPrice.CardType = new CardType((Guid)reader["CardTypeID"]);
                    cardPrice.DayNight = new DayNight(Boolean.Parse(reader["DayNightID"].ToString()));
                }
                return cardPrice;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool Delete(Guid id)
        {
            string strSQL = "DELETE FROM CardPrice WHERE ID=@ID";

            //parms
            SqlParameter parm = new SqlParameter("@ID", id);
            try
            {
                return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parm) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool IsExistCardPrice(CardPrice cardPrice) {
            #region Sql sentence
            string strSQL = @"Select count(DayNightID) from CardPrice WHERE DayNightID=@DayNightID AND CardTypeID=@CardTypeID";

            #endregion

            //parms
            SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@DayNightID",cardPrice.DayNight.Id),
                        new SqlParameter("@CardTypeID",cardPrice.CardType.Id)
                    };


            try
            {
               return Int16.Parse(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parms).ToString())>0;
            }
            catch (Exception)
            {
                throw;
            }
        } 
        #endregion


    }
